﻿using System;
using Pantry.Commons;
using Pantry.Entity;
using System.Data.SqlClient;
using System.Data;

namespace Pantry.DataAccess
{
    public class ServiceDao
    {
        private readonly SqlConnection _sqlConnection;

        public ServiceDao()
        {
            _sqlConnection = SqlConnectionTool.SqlGetConnection();
        }

        public ServiceDao(string sqlConnection)
        {
            _sqlConnection = new SqlConnection(sqlConnection);
        }

        #region Sql Command

        private const string QueryStringSelectAllService =@"
                        SELECT S.ID, S.[Name], ShortDescription, D.[Name] As ServiceType, 
                        C.FirstName + ' ' + C.SurName AS ContactName, 
                        CASE(S.IsActive) WHEN 1 THEN 'Yes' ELSE 'No' END AS IsActive
                        FROM Services S
                        INNER JOIN Contact C ON S.ContactID = C.ContactId
                        INNER JOIN ServiceType D ON D.ID = S.ServiceType
                        ORDER BY S.ID DESC";
        private const string QueryStringUpdateActive = @"
                    UPDATE Services SET IsActive = @isActive WHERE ID = @id";
        private const string QueryStringSelectServiceType = @"
                    SELECT ID, Name FROM ServiceType";
        private const string QueryStringSelectServiceSubType = @"
                    SELECT ID, Name FROM ServiceSubType";
        private const string QueryStringServiceAttendance = @"
                    SELECT ID, Name FROM ServiceAttendance";
        private const string QueryStringSelectReferralProcess = @"
                    SELECT ID, Name FROM ReferralProcess";
        private const string QueryStringInsertSerivce = @"
                    INSERT INTO Services(
                    Name, ShortDescription, ServiceType, ContactID, IsActive, ServiceSubType, FullDescription, DeptCode,
                    ClientDescription, DescriptionDelivery, ServiceAttendace, ServiceStartExpected, ServiceStart, ServiceEnd,
                    ServiceExtendable, ServiceContractCode, ServiceContactValue, ContractStagedPayment, ReferralProcessMethod,
                    ServiceTimeLimited, ServiceTimeLimitedMonth, ServiceExtendableMonth, SerBenCrit, SerBarCrit, SerEthCrit,
                    SerDisCrit, SerPerCirCrit, SerOrtherCrit, ClientSupportProcess, ClientOutcome, Intervention, TargetClient,
                    ClientJourney, Accreditation, OtherService, ReferralSources, ProgrammeID, SupportCenters, ContractOutcome,
                    Participartion, ContractObligation) VALUES(
                    @Name, @ShortDescription, @ServiceType, @ContactID, @IsActive, @ServiceSubType, @FullDescription,
                    @DeptCode, @ClientDescription, @DescriptionDelivery, @ServiceAttendace, @ServiceStartExpected,
                    @ServiceStart, @ServiceEnd, @ServiceExtendable, @ServiceContractCode, @ServiceContactValue,
                    @ContractStagedPayment, @ReferralProcessMethod, @ServiceTimeLimited, @ServiceTimeLimitedMonth,
                    @ServiceExtendableMonth, @SerBenCrit, @SerBarCrit, @SerEthCrit, @SerDisCrit, @SerPerCirCrit, 
                    @SerOrtherCrit, @ClientSupportProcess, @ClientOutcome, @Intervention, @TargetClient, @ClientJourney,
                    @Accreditation, @OtherService, @ReferralSources, @ProgrammeID, @SupportCenters, @ContractOutcome,
                    @Participartion, @ContractObligation)
                    SELECT ID = SCOPE_IDENTITY()";
        private const string QueryStringServiceById = @"
                    SELECT 
                    Name, ShortDescription, ServiceType, ContactID, IsActive, ServiceSubType, FullDescription, DeptCode,
                    ClientDescription, DescriptionDelivery, ServiceAttendace,convert(varchar(10),ServiceStartExpected,101) as ServiceStartExpected, 
                    convert(varchar(10),ServiceStart,101) as ServiceStart,
                    convert(varchar(10),ServiceEnd,101) as ServiceEnd,
                    ServiceExtendable, ServiceContractCode, ServiceContactValue, ContractStagedPayment, ReferralProcessMethod,
                    ServiceTimeLimited, ServiceTimeLimitedMonth, ServiceExtendableMonth, SerBenCrit, SerBarCrit, SerEthCrit,
                    SerDisCrit, SerPerCirCrit, SerOrtherCrit, ClientSupportProcess, ClientOutcome, Intervention, TargetClient,
                    ClientJourney, Accreditation, OtherService, ReferralSources, ProgrammeID, SupportCenters, ContractOutcome,
                    Participartion, ContractObligation
                    FROM Services WHERE ID = @id";
        private const string QueryStringUpdateService = @"
                    UPDATE Services SET Name=@Name, ShortDescription=@ShortDescription, ServiceType=@ServiceType,
                    ContactID=@ContactID, ServiceSubType=@ServiceSubType, FullDescription=@FullDescription, DeptCode=@DeptCode,
                    ClientDescription=@ClientDescription, DescriptionDelivery=@DescriptionDelivery, ServiceAttendace=@ServiceAttendace,
                    ServiceStartExpected=@ServiceStartExpected, ServiceStart=@ServiceStart, ServiceEnd=@ServiceEnd,
                    ServiceExtendable=@ServiceExtendable, ServiceContractCode=@ServiceContractCode, ServiceContactValue=@ServiceContactValue,
                    ContractStagedPayment=@ContractStagedPayment, ReferralProcessMethod=@ReferralProcessMethod, 
                    ServiceTimeLimited=@ServiceTimeLimited, ServiceTimeLimitedMonth=@ServiceTimeLimitedMonth,
                    ServiceExtendableMonth=@ServiceExtendableMonth, SerBenCrit=@SerBenCrit, SerBarCrit=@SerBarCrit,
                    SerEthCrit=@SerEthCrit, SerDisCrit=@SerDisCrit, SerPerCirCrit=@SerPerCirCrit, SerOrtherCrit=@SerOrtherCrit,
                    ClientSupportProcess=@ClientSupportProcess, ClientOutcome=@ClientOutcome, Intervention=@Intervention,
                    TargetClient=@TargetClient, ClientJourney=@ClientJourney, Accreditation=@Accreditation, OtherService=@OtherService,
                    ReferralSources=@ReferralSources, ProgrammeID=@ProgrammeID, SupportCenters=@SupportCenters, 
                    ContractOutcome=@ContractOutcome, Participartion=@Participartion, ContractObligation=@ContractObligation
                    WHERE ID=@id";
        private const string SqlGetServiceId =
           @"SELECT ID FROM Services WHERE Name = @Name";

        private const string QueryStringMarkInActiveService = @"
                    UPDATE Services
                    SET IsActive = 'FALSE'
                    WHERE ID = @ServiceId
                    ";
        #endregion

        #region Methods
        /// <summary>
        /// Selects the service id.
        /// </summary>
        /// <param name="name">The name.</param>
        /// <returns></returns>
        public DataTable SelectServiceId(string name)
        {
            var param = new SqlParameter[1];
            param[0] = new SqlParameter("@Name", SqlDbType.NVarChar) { Value = name };
            var ds = SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text, SqlGetServiceId, param);
            return ds.Tables[0];
        }
        /// <summary>
        /// Update the service.
        /// select service by id.
        /// </summary>
        /// <param name="id">The id.</param>
        public Service SelectServiceById(int id)
        {
            var param = new SqlParameter("@id", SqlDbType.Int) { Value = id };
            var dataRow = SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text, QueryStringServiceById, param).Tables[0].Rows[0];
            return new Service
            {
                Name = (string)dataRow["Name"],
                ShortDescription = (string)dataRow["ShortDescription"],
                ServiceType = (int)dataRow["ServiceType"],
                ContactId = (int)dataRow["ContactID"],
                ServiceSubType = (string)dataRow["ServiceSubType"],
                FullDescription = (string)dataRow["FullDescription"],
                DeptCode = (string)dataRow["DeptCode"],
                ClientDescription = (string)dataRow["ClientDescription"],
                DescriptionDelivery = (string)dataRow["DescriptionDelivery"],
                ServiceAttendace = (string)dataRow["ServiceAttendace"],
                ServiceStartExpected = (string)dataRow["ServiceStartExpected"],
                ServiceStart = (string)dataRow["ServiceStart"],
                ServiceEnd = (string)dataRow["ServiceEnd"],
                ServiceExtendable = (bool)dataRow["ServiceExtendable"],
                ServiceContractCode = (string)dataRow["ServiceContractCode"],
                ServiceContractValue = (string)dataRow["ServiceContactValue"],
                ContractStagedPayment = (bool)dataRow["ContractStagedPayment"],
                ReferralProcessMethod = (string)dataRow["ReferralProcessMethod"],
                ServiceTimeLimited = (bool)dataRow["ServiceTimeLimited"],
                ServiceTimeLimitedMonth = (int)dataRow["ServiceTimeLimitedMonth"],
                ServiceExtendableMonth = (int)dataRow["ServiceExtendableMonth"],
                ProgrammeId = (int)dataRow["ProgrammeID"],
                Participartion = (string)dataRow["Participartion"]
            };
        }

        /// <summary>
        /// Inserts the service.
        /// </summary>
        /// <param name="service">The service.</param>
        /// <returns></returns>
        public int InsertService(Service service)
        {
            try
            {
                var param = new SqlParameter[41];
                param[0] = new SqlParameter("@Name", SqlDbType.NVarChar, 100){Value = service.Name};
                param[1] = new SqlParameter("@ShortDescription", SqlDbType.Text){Value = service.ShortDescription};
                param[2] = new SqlParameter("@ServiceType", SqlDbType.Int) { Value = service.ServiceType };
                param[3] = new SqlParameter("@ContactID", SqlDbType.Int) { Value = service.ContactId };
                param[4] = new SqlParameter("@IsActive", SqlDbType.Bit) { Value = service.IsActive };
                param[5] = new SqlParameter("@ServiceSubType", SqlDbType.NVarChar, 100) { Value = service.ServiceSubType };
                param[6] = new SqlParameter("@FullDescription", SqlDbType.Text) { Value = service.FullDescription };
                param[7] = new SqlParameter("@DeptCode", SqlDbType.NVarChar, 100) { Value = service.DeptCode };
                param[8] = new SqlParameter("@ClientDescription", SqlDbType.Text) { Value = service.ClientDescription };
                param[9] = new SqlParameter("@DescriptionDelivery", SqlDbType.Text) { Value = service.DescriptionDelivery };
                param[10] = new SqlParameter("@ServiceAttendace", SqlDbType.NVarChar, 100) { Value = service.ServiceAttendace };
                param[11] = new SqlParameter("@ServiceStartExpected", SqlDbType.VarChar) { Value = service.ServiceStartExpected };
                param[12] = new SqlParameter("@ServiceStart", SqlDbType.VarChar) { Value = service.ServiceStart };
                param[13] = new SqlParameter("@ServiceEnd", SqlDbType.VarChar) { Value = service.ServiceEnd };
                param[14] = new SqlParameter("@ServiceExtendable", SqlDbType.Bit) { Value = service.ServiceExtendable };
                param[15] = new SqlParameter("@ServiceContractCode", SqlDbType.NVarChar, 30) { Value = service.ServiceContractCode };
                param[16] = new SqlParameter("@ServiceContactValue", SqlDbType.NVarChar) { Value = service.ServiceContractValue };
                param[17] = new SqlParameter("@ContractStagedPayment", SqlDbType.Bit) { Value = service.ContractStagedPayment };
                param[18] = new SqlParameter("@ReferralProcessMethod", SqlDbType.NVarChar, 100) { Value = service.ReferralProcessMethod };
                param[19] = new SqlParameter("@ServiceTimeLimited", SqlDbType.Bit) { Value = service.ServiceTimeLimited };
                param[20] = new SqlParameter("@ServiceTimeLimitedMonth", SqlDbType.Int) { Value = service.ServiceTimeLimitedMonth };
                param[21] = new SqlParameter("@ServiceExtendableMonth", SqlDbType.Int) { Value = service.ServiceExtendableMonth };
                param[22] = new SqlParameter("@SerBenCrit", SqlDbType.NVarChar, 200) { Value = service.SerBenCrit };
                param[23] = new SqlParameter("@SerBarCrit", SqlDbType.NVarChar, 200) { Value = service.SerBarCrit };
                param[24] = new SqlParameter("@SerEthCrit", SqlDbType.NVarChar, 200) { Value = service.SerEthCrit };
                param[25] = new SqlParameter("@SerDisCrit", SqlDbType.NVarChar, 200) { Value = service.SerDisCrit };
                param[26] = new SqlParameter("@SerPerCirCrit", SqlDbType.NVarChar, 200) { Value = service.SerPerCirCrit };
                param[27] = new SqlParameter("@SerOrtherCrit", SqlDbType.NVarChar, 200) { Value = service.SerOrtherCrit };
                param[28] = new SqlParameter("@ClientSupportProcess", SqlDbType.NVarChar, 200) { Value = service.ClientSupportProcess };
                param[29] = new SqlParameter("@ClientOutcome", SqlDbType.NVarChar, 200) { Value = service.ClientOutcome };
                param[30] = new SqlParameter("@Intervention", SqlDbType.NVarChar, 200) { Value = service.Intervention };
                param[31] = new SqlParameter("@TargetClient", SqlDbType.NVarChar, 200) { Value = service.TargetClient };
                param[32] = new SqlParameter("@ClientJourney", SqlDbType.NVarChar, 200) { Value = service.ClientJourney };
                param[33] = new SqlParameter("@Accreditation", SqlDbType.NVarChar, 200) { Value = service.Accreditation };
                param[34] = new SqlParameter("@OtherService", SqlDbType.NVarChar, 200) { Value = service.OtherService };
                param[35] = new SqlParameter("@ReferralSources", SqlDbType.NVarChar, 200) { Value = service.ReferralSources };
                param[36] = new SqlParameter("@ProgrammeID", SqlDbType.Int) { Value = service.ProgrammeId };
                param[37] = new SqlParameter("@SupportCenters", SqlDbType.NVarChar, 200) { Value = service.SupportCenters };
                param[38] = new SqlParameter("@ContractOutcome", SqlDbType.NVarChar, 200) { Value = service.ContractOutcome };
                param[39] = new SqlParameter("@Participartion", SqlDbType.NVarChar, 200) { Value = service.Participartion };
                param[40] = new SqlParameter("@ContractObligation", SqlDbType.NVarChar, 200) { Value = service.ContractObligation };
                return SqlHelper.ExecuteNonQuery(_sqlConnection, CommandType.Text, QueryStringInsertSerivce, param);
            }
            catch(ApplicationException e)
            {
                ExceptionHandler.WriteErrorLog(e);
                return -1;
            }
        }

        /// <summary>
        /// Updates the service.
        /// </summary>
        /// <param name="service">The service.</param>
        /// <returns></returns>
        public int UpdateService(Service service)
        {
            try
            {
                var param = new SqlParameter[42];
                param[0] = new SqlParameter("@Name", SqlDbType.NVarChar, 100) { Value = service.Name };
                param[1] = new SqlParameter("@ShortDescription", SqlDbType.Text) { Value = service.ShortDescription };
                param[2] = new SqlParameter("@ServiceType", SqlDbType.Int) { Value = service.ServiceType };
                param[3] = new SqlParameter("@ContactID", SqlDbType.Int) { Value = service.ContactId };
                param[4] = new SqlParameter("@IsActive", SqlDbType.Bit) { Value = service.IsActive };
                param[5] = new SqlParameter("@ServiceSubType", SqlDbType.NVarChar, 100) { Value = service.ServiceSubType };
                param[6] = new SqlParameter("@FullDescription", SqlDbType.Text) { Value = service.FullDescription };
                param[7] = new SqlParameter("@DeptCode", SqlDbType.NVarChar, 100) { Value = service.DeptCode };
                param[8] = new SqlParameter("@ClientDescription", SqlDbType.Text) { Value = service.ClientDescription };
                param[9] = new SqlParameter("@DescriptionDelivery", SqlDbType.Text) { Value = service.DescriptionDelivery };
                param[10] = new SqlParameter("@ServiceAttendace", SqlDbType.NVarChar, 100) { Value = service.ServiceAttendace };
                param[11] = new SqlParameter("@ServiceStartExpected", SqlDbType.VarChar) { Value = service.ServiceStartExpected };
                param[12] = new SqlParameter("@ServiceStart", SqlDbType.VarChar) { Value = service.ServiceStart };
                param[13] = new SqlParameter("@ServiceEnd", SqlDbType.VarChar) { Value = service.ServiceEnd };
                param[14] = new SqlParameter("@ServiceExtendable", SqlDbType.Bit) { Value = service.ServiceExtendable };
                param[15] = new SqlParameter("@ServiceContractCode", SqlDbType.NVarChar, 30) { Value = service.ServiceContractCode };
                param[16] = new SqlParameter("@ServiceContactValue", SqlDbType.NVarChar) { Value = service.ServiceContractValue };
                param[17] = new SqlParameter("@ContractStagedPayment", SqlDbType.Bit) { Value = service.ContractStagedPayment };
                param[18] = new SqlParameter("@ReferralProcessMethod", SqlDbType.NVarChar, 100) { Value = service.ReferralProcessMethod };
                param[19] = new SqlParameter("@ServiceTimeLimited", SqlDbType.Bit) { Value = service.ServiceTimeLimited };
                param[20] = new SqlParameter("@ServiceTimeLimitedMonth", SqlDbType.Int) { Value = service.ServiceTimeLimitedMonth };
                param[21] = new SqlParameter("@ServiceExtendableMonth", SqlDbType.Int) { Value = service.ServiceExtendableMonth };
                param[22] = new SqlParameter("@SerBenCrit", SqlDbType.NVarChar, 200) { Value = service.SerBenCrit };
                param[23] = new SqlParameter("@SerBarCrit", SqlDbType.NVarChar, 200) { Value = service.SerBarCrit };
                param[24] = new SqlParameter("@SerEthCrit", SqlDbType.NVarChar, 200) { Value = service.SerEthCrit };
                param[25] = new SqlParameter("@SerDisCrit", SqlDbType.NVarChar, 200) { Value = service.SerDisCrit };
                param[26] = new SqlParameter("@SerPerCirCrit", SqlDbType.NVarChar, 200) { Value = service.SerPerCirCrit };
                param[27] = new SqlParameter("@SerOrtherCrit", SqlDbType.NVarChar, 200) { Value = service.SerOrtherCrit };
                param[28] = new SqlParameter("@ClientSupportProcess", SqlDbType.NVarChar, 200) { Value = service.ClientSupportProcess };
                param[29] = new SqlParameter("@ClientOutcome", SqlDbType.NVarChar, 200) { Value = service.ClientOutcome };
                param[30] = new SqlParameter("@Intervention", SqlDbType.NVarChar, 200) { Value = service.Intervention };
                param[31] = new SqlParameter("@TargetClient", SqlDbType.NVarChar, 200) { Value = service.TargetClient };
                param[32] = new SqlParameter("@ClientJourney", SqlDbType.NVarChar, 200) { Value = service.ClientJourney };
                param[33] = new SqlParameter("@Accreditation", SqlDbType.NVarChar, 200) { Value = service.Accreditation };
                param[34] = new SqlParameter("@OtherService", SqlDbType.NVarChar, 200) { Value = service.OtherService };
                param[35] = new SqlParameter("@ReferralSources", SqlDbType.NVarChar, 200) { Value = service.ReferralSources };
                param[36] = new SqlParameter("@ProgrammeID", SqlDbType.Int) { Value = service.ProgrammeId };
                param[37] = new SqlParameter("@SupportCenters", SqlDbType.NVarChar, 200) { Value = service.SupportCenters };
                param[38] = new SqlParameter("@ContractOutcome", SqlDbType.NVarChar, 200) { Value = service.ContractOutcome };
                param[39] = new SqlParameter("@Participartion", SqlDbType.NVarChar, 200) { Value = service.Participartion };
                param[40] = new SqlParameter("@ContractObligation", SqlDbType.NVarChar, 200) { Value = service.ContractObligation };
                param[41] = new SqlParameter("@id", SqlDbType.Int) { Value = service.Id };
                return SqlHelper.ExecuteNonQuery(_sqlConnection, CommandType.Text, QueryStringUpdateService, param);
            }
            catch (SqlException e)
            {
                ExceptionHandler.WriteErrorLog(e);
                throw;
            }
        }

        /// <summary>
        /// Selects all service.
        /// </summary>
        /// <returns></returns>
        public DataTable SelectAllService()
        {
            var dataTable = SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text, QueryStringSelectAllService);
            return dataTable.Tables[0];
        }

        /// <summary>
        /// Selects the type of the service.
        /// </summary>
        /// <returns></returns>
        public DataTable SelectServiceType()
        {
            var dataTable = SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text, QueryStringSelectServiceType);
            return dataTable.Tables[0];
        }

        /// <summary>
        /// Selects the type of the service sub.
        /// </summary>
        /// <returns></returns>
        public DataTable SelectServiceSubType()
        {
            var dataTable = SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text, QueryStringSelectServiceSubType);
            return dataTable.Tables[0];
        }

        /// <summary>
        /// Selects the service attendance.
        /// </summary>
        /// <returns></returns>
        public DataTable SelectServiceAttendance()
        {
            var dataTable = SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text, QueryStringServiceAttendance);
            return dataTable.Tables[0];
        }

        /// <summary>
        /// Selects the referral process.
        /// </summary>
        /// <returns></returns>
        public DataTable SelectReferralProcess()
        {
            var dataTable = SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text, QueryStringSelectReferralProcess);
            return dataTable.Tables[0];
        }

        /// <summary>
        /// Marks the activated.
        /// </summary>
        /// <param name="isActive">if set to <c>true</c> [is active].</param>
        /// <param name="id">The id.</param>
        /// <returns></returns>
        public int MarkActivated(bool isActive, int id)
        {
            try
            {
                var parameters = new SqlParameter[2];
                parameters[0] = new SqlParameter("@isActive", SqlDbType.Bit) { Value = isActive };
                parameters[1] = new SqlParameter("@id", SqlDbType.Int) { Value = id };
                return SqlHelper.ExecuteNonQuery(_sqlConnection, CommandType.Text, QueryStringUpdateActive, parameters);
            }
            catch (SqlException sqlException)
            {
                ExceptionHandler.WriteErrorLog(sqlException);
                return 0;
            }
            catch(ArgumentException exception)
            {
                ExceptionHandler.WriteErrorLog(exception);
                return 0;
            }
        }
        #endregion
    }
}
